﻿using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using MODEL;
using System.Data;
namespace DAL
{
    public class DalHistoryStockDetail
    {
        public int IsExist(int dicid, int historyid)
        {
            string sql = "select id from historystockdetail where dicid=@dicid and historyid=@historyid";
            return Convert.ToInt32(AccessHelp.ExecuteScalar(sql, new OleDbParameter("@dicid", dicid), new OleDbParameter("@historyid", historyid)));
        }

        public List<historystockdetail> GetAllListByHisId(int hisid)
        {
            string sql = "select id,historyid,beginnumber,totalin,totalout,totalreturn,goodsnumber,dicid,goodsname,classid,goodspy from historystockdetail where historyid=@hisid";
            DataTable dt = AccessHelp.ExecuteReader(sql,new OleDbParameter("@hisid",hisid));
            return TableToList(dt);
        }
        public DataTable GetTablesByHisId(int hisid)
        {
            string sql = "select h.id,d.classname,d.goodsname,d.goodsmodel,d.goodsspe,d.goodsunit,h.historyid,h.beginnumber,h.totalin,h.totalout,h.totalreturn,h.goodsnumber,h.dicid,d.classid,d.goodspy,d.goodprice from historystockdetail h inner join dic d on d.id = h.dicid where historyid=@hisid order by d.classid,d.id";
            DataTable dt = AccessHelp.ExecuteReader(sql, new OleDbParameter("@hisid", hisid));
            return dt;
            //return TableToList(dt);
        }
        public int Insert(historystockdetail hsd)
        {
            //s.Number = s.Number + new BllStock().GetById(b).Number;
            string hissql = "insert into historystockdetail(historyid,beginnumber,totalin,goodsnumber,dicid,goodsname,classid,goodspy) values(@historyid,@beginnumber,@totalin,@goodsnumber,@dicid,@goodsname,@classid,@goodspy)";
            OleDbParameter[] hispms = new OleDbParameter[]
                {
                    new OleDbParameter("@historyid",hsd.HistoryId),
                    new OleDbParameter("@beginnumber",hsd.BeginNumber),
                    new OleDbParameter("@totalin",hsd.TotalIn),
                    new OleDbParameter("@goodsnumber",hsd.GoodsNumber),
                    new OleDbParameter("@dicid",hsd.DicId),
                    new OleDbParameter("@goodsname",hsd.GoodsNumber),
                    new OleDbParameter("@classid",hsd.ClassId),
                    new OleDbParameter("@goodspy",hsd.Goodspy) 
                };
            return AccessHelp.ExecteNonQuery(hissql, hispms);
        }
        public List<historystockdetail> TableToList(DataTable dt)
        {
            List<historystockdetail> hisDetails = new List<historystockdetail>();
            foreach (DataRow row in dt.Rows)
            {
                historystockdetail hstockDeltails = new historystockdetail();
                hstockDeltails.Id = (int)row[0];
                hstockDeltails.HistoryId = (int)row[1];
                hstockDeltails.BeginNumber = (int)row[2];
                hstockDeltails.TotalIn = (int)row[3];
                hstockDeltails.TotalOut = (int)row[4];
                hstockDeltails.TotalReturn = (int)row[5];
                hstockDeltails.GoodsNumber = (int)row[6];
                hstockDeltails.DicId = (int)row[7];
                hstockDeltails.GoodsName = row[8].ToString();
                hstockDeltails.ClassId = (int)row[9];
                hstockDeltails.Goodspy = row[10].ToString();
                hisDetails.Add(hstockDeltails);
            }
            return hisDetails;
        }

        public int Delete(historystockdetail hisdetails)
        {
            string sql = "delete from historystockdetail where id=@id";
            return AccessHelp.ExecteNonQuery(sql, new OleDbParameter("@id", hisdetails.Id));
        }

        public int Update(historystockdetail hisdetails)
        {
            string sql = "update historystockdetail set beginnumber=@beginnumber,totalin=@totalin,totalout=@totalout,totalreturn=@totalreturn,goodsnumber=@goodsnumber where id=@id";
            OleDbParameter[] pms = new OleDbParameter[]
            {
                new OleDbParameter("@beginnumber",hisdetails.BeginNumber),
                new OleDbParameter("@totalin",hisdetails.TotalIn),
                new OleDbParameter("@totalout",hisdetails.TotalOut),
                new OleDbParameter("@totalreturn",hisdetails.TotalReturn),
                new OleDbParameter("@goodsnumber",hisdetails.GoodsNumber),
                new OleDbParameter("@id",hisdetails.Id)
            };
            return AccessHelp.ExecteNonQuery(sql,pms);
        }

        public historystockdetail GetObjectById(int hbid) 
        {
            string sql = "select id,historyid,beginnumber,totalin,totalout,totalreturn,goodsnumber,dicid,goodsname,classid,goodspy from historystockdetail where id=@id";
            DataTable dt = AccessHelp.ExecuteReader(sql, new OleDbParameter("@id", hbid));
            return TableToList(dt).FirstOrDefault();  
        }
    }
}
